package com.matt.repository.cookbook.impl;

import com.matt.domain.cookbook.Recipe;
import com.matt.domain.cookbook.RecipeFood;
import com.matt.domain.cookbook.RecipeIngredients;
import com.matt.domain.cookbook.RecipeShow;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.cookbook.RecipeNativeRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class RecipeNativeRepositoryImpl extends BaseNativeSqlRepository implements RecipeNativeRepository {

    @Override
    public List<RecipeFood> selectRecipeFoodListByCondition(Map<String, Object> filter) {
        String areaId = (String) filter.get("areaId");
        String type = (String) filter.get("type") ;
        String name = (String) filter.get("name") ;
        String query = "select * from tb_recipe_food where 1=1 " +
                ( StringUtils.isNotEmpty(areaId) ? (String.format("and area_id = '%s' ", areaId)) : "") +
                ( type != null ? (String.format("and type = %s ", type)) : "") +
                ( StringUtils.isNotEmpty(name) ? (String.format("and name = '%s' ", name)) : "") +
                "order by id desc";

        return sqlObjectList(query, RecipeFood.class);
    }

    @Override
    public List<Map<String, Object>> selectRecipeFoodByFilter(Map<String, Object> filter) {
        String areaId = (String) filter.get("areaId");
        String recipeDate = (String) filter.get("recipeDate");
        Integer recipeTypeId = (Integer) filter.get("recipeTypeId") ;

        String query = "select a.recipe_type_id as recipeTypeId,a.recipe_type_name as recipeTypeName,a.food_id as foodId," +
                "b.name as foodName,b.photo from tb_recipe a, tb_recipe_food b where 1=1 and a.food_id =b.id " +
                ( StringUtils.isNotEmpty(areaId) ? (String.format("and a.area_id = '%s' ", areaId)) : "") +
                ( StringUtils.isNotEmpty(recipeDate) ? (String.format("and a.recipe_date = '%s' ", recipeDate)) : "") +
                ( recipeTypeId != null ? (String.format("and a.recipe_type_id = %s ", recipeTypeId)) : "") +
                "order by a.recipe_type_id asc";

        return sqlObjectList(query, HashMap.class);
    }

    @Override
    public List<RecipeIngredients> selectRecipeIngredientsByCondition(Map<String, Object> filter) {
        String areaId = (String) filter.get("areaId");
        String startTime = (String) filter.get("startTime") ;
        String endTime = (String) filter.get("endTime") ;

        String query = "select * from tb_recipe_ingredients where 1=1 " +
                ( StringUtils.isNotEmpty(areaId) ? (String.format("and area_id = '%s' ", areaId)) : "") +
                ( StringUtils.isNotEmpty(startTime) ? (String.format("and start_date >= '%s' ", startTime)) : "") +
                ( StringUtils.isNotEmpty(endTime) ? (String.format("and end_date <= '%s' ", endTime)) : "");

        return sqlObjectList(query, RecipeIngredients.class);
    }

    @Override
    public List<Map<String, Object>> selectRecipeFoodByTime(Map<String, Object> filter) {
        String areaId = (String) filter.get("areaId");
        String startTime = (String) filter.get("startTime") ;
        String endTime = (String) filter.get("endTime") ;
        String recipeTypeId = (String) filter.get("recipeTypeId") ;

        String query = "SELECT a.id,a.recipe_date,a.recipe_type_id,a.recipe_type_name,a.food_id, b.`name` food_name," +
                "b.photo,b.type food_type,type_name  FROM `tb_recipe` a , tb_recipe_food b , tb_recipe_config c " +
                "where a.food_id = b.id and c.type= 'recipe_type' and c.value= a.recipe_type_id and c.deleted=false " +
                String.format( "and a.area_id = '%s' and c.area_id = '%s' ", areaId, areaId) +
                ( recipeTypeId != null ? (String.format("and a.recipe_type_id = %s ", recipeTypeId)) : "") +
                String.format("and a.recipe_date >= '%s' and a.recipe_date <= '%s' ", startTime, endTime) +
                "order by a.recipe_date , c.sort_id ,a.id asc";

        return sqlObjectList(query, HashMap.class);
    }

    @Override
    public List<Recipe> selectRecipeListByCondition(Map<String, Object> filter) {
        String areaId = (String) filter.get("areaId");
        String startTime = (String) filter.get("startTime") ;
        String endTime = (String) filter.get("endTime") ;
        Long recipeTypeId = (Long) filter.get("recipeTypeId") ;
        Long foodId = (Long) filter.get("foodId") ;

        String query = "select * from tb_recipe where 1=1 " +
                ( StringUtils.isNotEmpty(areaId) ? (String.format("and area_id = '%s' ", areaId)) : "") +
                ( recipeTypeId != null ? (String.format("and recipe_type_id = %s ", recipeTypeId)) : "") +
                ( foodId != null ? (String.format("and food_id = %s ", foodId)) : "") +
                ( StringUtils.isNotEmpty(startTime) ? (String.format("and recipe_date >= '%s' ", startTime)) : "") +
                ( StringUtils.isNotEmpty(endTime) ? (String.format("and recipe_date <= '%s' ", endTime)) : "") +
                "order by recipe_date , recipe_type_id asc ";

        return sqlObjectList(query, Recipe.class);
    }

    @Override
    public String selectEarlyRecipeDateByAreaId(String areaId) {

        String query = "select distinct  date(recipe_date) recipeTime from tb_recipe a " +
                String.format("where a.area_id = '%s' ", areaId) +
                "order by date(recipe_date) asc limit 1";

        List<String> result = sqlSingleList(query);

        if(result == null || result.size() == 0){
            return null;
        }

        return result.get(0);
    }

    @Override
    public List<String> selectAreaIdsByTime(String begin, String end) {
        String query = "select distinct a.school_id from  tb_recipe a where 1=1 " +
                String.format("and a.recipe_date >= '%s' and a.recipe_date <= '%s'", begin, end);

        return sqlSingleList(query);
    }

    @Override
    public List<Map<String, Object>> selectRecipeTypeIdByTimeAndAreaId(String recipeDate, String areaId) {
        String query = "select distinct a.recipe_type_id as recipeTypeId , a.recipe_type_name as recipeTypeName " +
                "from  tb_recipe a  ,tb_recipe_config b where true " +
                String.format("and a.recipe_date = '%s' ", recipeDate )+
                String.format("and a.area_id = '%s' ", areaId) +
                "and  b.type = 'recipe_type' " +
                "and  a.`recipe_type_id` = b.`value` " +
                "order by b.`sort_id`  asc ;";

        return sqlObjectList(query, HashMap.class);
    }

    @Override
    public List<RecipeShow> selectRecipeShowListByCondition(Map<String, Object> filter) {
        String areaId = (String) filter.get("areaId");
        Integer publishFlag = (Integer) filter.get("publishFlag") ;
        String startTime = (String) filter.get("startTime") ;
        String endTime = (String) filter.get("endTime") ;

        String query = "select * from tb_recipe_show where 1=1 " +
                ( StringUtils.isNotEmpty(areaId) ? (String.format("and area_id = '%s' ", areaId)) : "") +
                ( publishFlag != null ? (String.format("and publish_flag = %s ", publishFlag)) : "") +
                ( StringUtils.isNotEmpty(startTime) ? (String.format("and recipe_date >= '%s' ", startTime)) : "") +
                ( StringUtils.isNotEmpty(endTime) ? (String.format("and recipe_date <= '%s' ", endTime)) : "") +
                " order by recipe_date asc";

        return sqlObjectList(query, RecipeShow.class);
    }
}
